package org.yoao.crm.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.yoao.crm.DBManager.DBManager;
import org.yoao.crm.bean.StaticBean;
import org.yoao.crm.bean.User;
import org.yoao.crm.tools.TimeTool;

public class UserDAO extends DBManager {
	private static final Logger log = Logger.getLogger(UserDAO.class);

	private User findSingle(Map data) throws Exception {
		User user = null;
		if (data != null && !data.isEmpty()) {
			user = new User();
			user.setId(Integer.parseInt(data.get("ID").toString()));
			user.setUserName((String) data.get("USERNAME"));
			user.setNickName((String) data.get("NICKNAME"));
			user.setPassWord((String) data.get("PASSWORD"));
			user.setRole((String) data.get("ROLEID"));
			user.setAccessSno((String) data.get("ACCESSSNO"));
			user.setPhone((String) data.get("PHONE"));
			user.setAddress((String) data.get("ADDRESS"));
			user.setCompanyId((String) data.get("COMPANYID"));
			user.setPosition((String) data.get("POSITION"));
			user.setCreateTime((String) data.get("CREATETIME"));
			user.setIsAudit(Integer.parseInt(data.get("ISAUDIT").toString()));
		}
		return user;
	}

	public User findUserByName(String userName) throws Exception {
		String sql = "select * from baseuser where userName='" + userName + "'";
		return findSingle(first(sql));
	}

	public User findById(String id) throws Exception {
		String sql = "select * from baseuser where id=" + id;
		return findSingle(querySingleRow(sql));
	}

	public User findBySno(String userSno) throws Exception {
		String sql = "select * from baseuser where userName='" + userSno + "'";
		return findSingle(querySingleRow(sql));
	}

	public Map findRoleByUser(String userSno) throws Exception {
		String sql = "select baseuser.roleId, userRole.roleName from baseuser "
				+ "left join  userRole on userRole.roleId = baseuser.roleId "
				+ "where baseuser.username='" + userSno + "'";
		return querySingleRow(sql);
	}

	public int getDataCount(Map map) throws Exception {
		String sql = "select count(id) as userCount from baseuser";
		String where = " where ";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				String value = map.get(key).toString();
				if (key.indexOf(" LIKE ") < 1) {
					where = where + key + "='" + value + "' and ";
				} else {
					where = where + key + " '%" + value + "%' and ";
				}
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(" where "))
			where = "";
		sql += where;
		String result = querySingleValue(sql);
		if (result == null || result.trim().equals(""))
			result = "0";
		return Integer.parseInt(result);
	}

	public List find(int startRow, int endRow, Map map) throws Exception {
		String sql = "select top "
				+ endRow
				+ " baseuser.ID, baseuser.USERNAME, baseuser.NICKNAME, "
				+ "userRole.roleName as ROLEID, baseuser.ACCESSSNO, baseuser.PHONE, baseuser.ADDRESS, "
				+ "company.companyName as COMPANYID, baseuser.POSITION, baseuser.CREATETIME, "
				+ "baseuser.ISAUDIT from baseuser "
				+ "left join userRole on userRole.roleId = baseuser.roleid "
				+ "left join company on company.companyId = baseuser.companyId "
				+ "where baseuser.ID not in(";
		String filterSql = "select top " + startRow
				+ " b2.ID from baseuser as b2 ";

		String where = "";
		String fwhere = "";
		if (map != null && !map.isEmpty()) {
			Iterator iter = map.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next().toString();
				String value = map.get(key).toString();
				if (key.indexOf(" LIKE ") < 1) {
					where = where + "baseuser." + key + "='" + value + "' and ";
					fwhere = fwhere + "b2." + key + "='" + value + "' and ";
				} else {
					where = where + "baseuser." + key + " '%" + value
							+ "%' and ";
					fwhere = fwhere + "b2." + key + " '%" + value + "%' and ";
				}
			}
		}
		if (where.endsWith(" and "))
			where = where.substring(0, where.length() - 4);
		if (where.equals(""))
			where = null;
		if (fwhere.endsWith(" and "))
			fwhere = fwhere.substring(0, fwhere.length() - 4);
		if (fwhere.equals(""))
			fwhere = null;

		if (where == null) {
			sql = sql + filterSql
					+ " order by b2.id desc) order by baseuser.ID desc";
		} else {
			filterSql = filterSql + " where " + fwhere;
			sql = sql + filterSql + " order by b2.id desc) and " + where
					+ " order by baseuser.ID desc";
		}
		return find(sql);
	}

	private List find(String sql) {
		log.debug(sql);
		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
		List result = new ArrayList();
		try {
			con = getConnection();
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			String columnName = null;
			while (rs.next()) {
				User user = new User();
				for (int i = 1; i <= columnCount; i++) {
					columnName = rsmd.getColumnName(i);
					if (columnName.equalsIgnoreCase("ID"))
						user.setId(rs.getInt(columnName));
					if (columnName.equalsIgnoreCase("USERNAME"))
						user.setUserName(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("NICKNAME"))
						user.setNickName(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("ROLEID"))
						user.setRole(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("ACCESSSNO"))
						user.setAccessSno(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("PHONE"))
						user.setPhone(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("ADDRESS"))
						user.setAddress(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("COMPANYID"))
						user.setCompanyId(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("POSITION"))
						user.setPosition(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("CREATETIME"))
						user.setCreateTime(rs.getString(columnName));
					if (columnName.equalsIgnoreCase("ISAUDIT"))
						user.setIsAudit(rs.getInt(columnName));
				}
				result.add(user);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			result = null;
			log.error("ERROR SQL:" + sql);
			e.printStackTrace();
		} finally {
			closeConnection(con);
			return result;
		}
	}

	public int insert(User user) throws Exception {
		String sql = "insert into baseuser(USERNAME, NICKNAME, PASSWORD, ROLEID, ACCESSSNO, PHONE, ADDRESS, "
				+ "COMPANYID, POSITION, CREATETIME, ISAUDIT)values('"
				+ user.getUserName()
				+ "', '"
				+ user.getNickName()
				+ "', '"
				+ user.getPassWord()
				+ "', '"
				+ user.getRole()
				+ "', '"
				+ user.getAccessSno()
				+ "', '"
				+ user.getPhone()
				+ "', '"
				+ user.getAddress()
				+ "', '"
				+ user.getCompanyId()
				+ "', '"
				+ user.getPosition() + "', '" + user.getCreateTime() + "', 0)";
		int result = -1;
		if (update(sql) > -1) {
			sql = "select id from baseUser where USERNAME='"
					+ user.getUserName() + "'";
			String r = querySingleValue(sql);
			if (r != null && !r.trim().equals("")) {
				result = Integer.parseInt(r);
			}
		}
		return result;
	}

	public int modify(User user) throws Exception {
		String sql = "update baseuser set NICKNAME='" + user.getNickName()
				+ "', ROLEID='" + user.getRole() + "', ACCESSSNO='"
				+ user.getAccessSno() + "', PHONE='" + user.getPhone()
				+ "', ADDRESS='" + user.getAddress() + "', COMPANYID='"
				+ user.getCompanyId() + "', POSITION='" + user.getPosition()
				+ "'";
		if (user.getPassWord() != null && !user.getPassWord().trim().equals("")) {
			sql += ", passWord='" + user.getPassWord() + "'";
		}
		sql += " where USERNAME='" + user.getUserName() + "'";
		return update(sql);
	}

	public int delete(String id) throws Exception {
		String sql = "delete from baseuser where id=" + id;
		return update(sql);
	}

	public int modifyPassWord(String nickName, String userName, String passWord)
			throws Exception {
		String sql = "update baseuser set nickName='" + nickName
				+ "', passWord = '" + passWord + "' where userName='"
				+ userName + "'";
		return update(sql);
	}

	public int audit(String id, int value) throws Exception {
		String sql = "update baseuser set ISAUDIT=" + value + " where id=" + id;
		return update(sql);
	}

	public synchronized String create() {
		try {
			log.info("开始初始化表baseuser...");
			String sql = "SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[baseuser]');";
			List list = this.queryMultiColumn(sql);
			if (list == null) {
				log
						.warn("系统检测失败。\n原因:\n  1.数据库非正常运行;\n  2.帐户没权限不足;\n  3.数据库连接异常。");
				return "系统检测失败。\n原因:\n  1.数据库非正常运行;\n  2.帐户没权限不足;\n  3.数据库连接异常。";
			}
			if (list.isEmpty()) {
				log.info("表baseuser不存在，开始创建...");
				sql = "/****** 对象:  Table [dbo].[baseuser]    脚本日期: 06/24/2009 17:40:16 ******/ "
						+ "SET ANSI_NULLS ON;"
						+ "SET QUOTED_IDENTIFIER ON;"
						+ "SET ANSI_PADDING ON;"
						+ "CREATE TABLE [dbo].[baseuser]("
						+ "[id] [int] IDENTITY(1,1) NOT NULL,"
						+ "[USERNAME] [varchar](50) NOT NULL,"
						+ "[NICKNAME] [varchar](50) NOT NULL,"
						+ "[PASSWORD] [varchar](50) NOT NULL,"
						+ "[ROLEID] [varchar](50) NOT NULL,"
						+ "[ACCESSSNO] [varchar](500) NULL,"
						+ "[PHONE] [varchar](20) NULL,"
						+ "[ADDRESS] [varchar](100) NULL,"
						+ "[COMPANYID] [varchar](50) NULL,"
						+ "[POSITION] [varchar](50) NULL,"
						+ "[CREATETIME] [varchar](50) NULL,"
						+ "[ISAUDIT] [int] NOT NULL CONSTRAINT [DF_baseuser_ISAUDIT]  DEFAULT ((0)),	"
						+ "CONSTRAINT [PK_baseuser] PRIMARY KEY CLUSTERED"
						+ "("
						+ "[id] ASC"
						+ ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]"
						+ ") ON [PRIMARY];" + "SET ANSI_PADDING OFF;";

				if (this.update(sql) < 0) {
					log.warn("表baseuser初始化失败!");
					return "表baseuser初始化失败!";
				}
				log.info("表baseuser创建成功!");
				log.info("给表baseuser初始化数据!");
				sql = "insert into baseuser(USERNAME, NICKNAME, PASSWORD, ROLEID, ACCESSSNO, PHONE, ADDRESS, COMPANYID, POSITION, CREATETIME, ISAUDIT) values('admin', '系统管理员', 'password', '"
						+ StaticBean.ROLE_ADMIN
						+ "', '"
						+ StaticBean.ROLE_SYSTEM
						+ "', '13817818683', '上海市浦东新区', '"
						+ StaticBean.CODE_HQ
						+ "', '工程师', '" + TimeTool.getSystemTime() + "', 1)";
				update(sql);
				return "表baseuser创建成功!";
			} else {
				log.info("表baseuser已存在!");
				return "表baseuser已存在!";
			}
		} catch (Exception e) {
			log.warn("表baseuser初始化失败!");
			e.printStackTrace();
			return "表baseuser初始化失败!" + e.toString();
		}
	}

	public Map first(String sql) {
		if (sql == null || sql.trim().equals(""))
			return null;
		log.debug(sql);
		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
		Map result = new HashMap();
		try {
			con = getConnection();
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			String columnName = null;
			while (rs.next()) {
				for (int i = 1; i <= columnCount; i++) {
					columnName = rsmd.getColumnName(i);
					result.put(columnName.toUpperCase(), rs
							.getString(columnName));
				}
			}
		} catch (SQLException e) {
			if (e.getMessage().equals("对象名 'baseuser' 无效。")) { // 系统第一次运行,创建用户表
				System.out.println("系统第一次运行,创建用户表...");
				create();
				new RoleDAO().create();
				System.out.println("重新登录...");
				result = querySingleRow(sql);
			} else {
				result = null;
				log.error("ERROR SQL:" + sql);
				e.printStackTrace();
			}
		} finally {
			closeConnection(con);
			return result;
		}
	}
}